/**
 * 
 */
package wpmp.security.mgr.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Component;

import wpmp.security.mgr.base.BaseDao;
import wpmp.security.mgr.entity.Org;

/**
 * @author Wayne.Wang<5waynewang@gmail.com>
 * @since 8:31:30 PM Feb 25, 2013
 */
@Component
public class OrgDao extends BaseDao<Org, Long> {

	private static final String SQL_DELETE_BY_LEVEL = "DELETE FROM sys_org WHERE level LIKE concat(?,'%')";

	private static final String SQL_LIST_CHILD_IS_LEAF = "SELECT id,NOT EXISTS(SELECT 1 FROM sys_org x WHERE x.parent_id=a.id) is_leaf FROM sys_org a WHERE a.parent_id=?";

	private static final String SQL_INCREASE_ORDER_NUM_WHEN_GE = "UPDATE sys_org SET order_num=order_num+1 WHERE parent_id=? AND order_num>=?";

	private static final String SQL_INCREASE_ORDER_NUM_WHEN_BETWEEN = "UPDATE sys_org SET order_num=order_num+1 WHERE parent_id=? AND order_num BETWEEN ? AND ?";

	private static final String SQL_DECREASE_ORDER_NUM_WHEN_BETWEEN = "UPDATE sys_org SET order_num=order_num-1 WHERE parent_id=? AND order_num BETWEEN ? AND ?";

	private static final String SQL_DECREASE_ORDER_NUM_WHEN_GT = "UPDATE sys_org SET order_num=order_num-1 WHERE parent_id=? AND order_num>?";

	private static final String SQL_UPDATE_CHILD_LEVEL = "UPDATE sys_org SET level=CONCAT(?,SUBSTR(level FROM ?)) WHERE level LIKE CONCAT(?,'%') AND level<>?";

	private static final String SQL_LIST = "SELECT id,name,level FROM sys_org ORDER BY level,order_num";

	private static final String SQL_GET = "SELECT id,name,level FROM sys_org WHERE id=?";

	/**
	 * 根据ID查询组织/单位列表
	 * 
	 * @return
	 */
	public SqlRowSet queryForRowSet(final Long id) {
		return getJdbcTemplate().queryForRowSet(SQL_GET, id);
	}

	/**
	 * 查询所有的组织/单位列表
	 * 
	 * @return
	 */
	public SqlRowSet queryForRowSet() {
		return getJdbcTemplate().queryForRowSet(SQL_LIST);
	}

	/**
	 * 更新子类的level
	 * 
	 * <pre>
	 * 同时更新所有直系子类的level
	 * </pre>
	 * 
	 * @param fromLevel
	 * @param toLevel
	 * @return
	 */
	public int updateChildLevel(final String fromLevel, final String toLevel) {
		return getJdbcTemplate().execute(SQL_UPDATE_CHILD_LEVEL,
				new PreparedStatementCallback<Integer>() {
					@Override
					public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException,
							DataAccessException {
						ps.setString(1, toLevel);
						ps.setInt(2, fromLevel.length() + 1);
						ps.setString(3, fromLevel);
						ps.setString(4, fromLevel);
						return ps.executeUpdate();
					}
				});
	}

	/**
	 * 当orderNum大于或等于某值的时候递增
	 * 
	 * @param parentId
	 * @param orderNum
	 * @return
	 */
	public int increaseOrderNumWhenGe(final Long parentId, final Integer orderNum) {
		return getJdbcTemplate().execute(SQL_INCREASE_ORDER_NUM_WHEN_GE,
				new PreparedStatementCallback<Integer>() {
					@Override
					public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException,
							DataAccessException {
						ps.setLong(1, parentId);
						ps.setInt(2, orderNum);
						return ps.executeUpdate();
					}
				});
	}

	/**
	 * 当orderNum在某区间的时候递增
	 * 
	 * @param parentId
	 * @param bgnOrderNum
	 * @param endOrderNum
	 * @return
	 */
	public int increaseOrderNumWhenBetween(final Long parentId, final Integer bgnOrderNum,
			final Integer endOrderNum) {
		return getJdbcTemplate().execute(SQL_INCREASE_ORDER_NUM_WHEN_BETWEEN,
				new PreparedStatementCallback<Integer>() {
					@Override
					public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException,
							DataAccessException {
						ps.setLong(1, parentId);
						ps.setInt(2, bgnOrderNum);
						ps.setInt(3, endOrderNum);
						return ps.executeUpdate();
					}
				});
	}

	/**
	 * 当orderNum在某区间的时候递减
	 * 
	 * @param parentId
	 * @param bgnOrderNum
	 * @param endOrderNum
	 * @return
	 */
	public int decreaseOrderNumWhenBetween(final Long parentId, final Integer bgnOrderNum,
			final Integer endOrderNum) {
		return getJdbcTemplate().execute(SQL_DECREASE_ORDER_NUM_WHEN_BETWEEN,
				new PreparedStatementCallback<Integer>() {
					@Override
					public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException,
							DataAccessException {
						ps.setLong(1, parentId);
						ps.setInt(2, bgnOrderNum);
						ps.setInt(3, endOrderNum);
						return ps.executeUpdate();
					}
				});
	}

	/**
	 * 当orderNum大于某值的时候递减
	 * 
	 * @param parentId
	 * @param orderNum
	 * @return
	 */
	public int decreaseOrderNumWhenGt(final Long parentId, final Integer orderNum) {
		return getJdbcTemplate().execute(SQL_DECREASE_ORDER_NUM_WHEN_GT,
				new PreparedStatementCallback<Integer>() {
					@Override
					public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException,
							DataAccessException {
						ps.setLong(1, parentId);
						ps.setInt(2, orderNum);
						return ps.executeUpdate();
					}
				});
	}

	/**
	 * 删除子类
	 * 
	 * @param level
	 * @return
	 */
	public int deleteByLevel(final String level) {
		return getJdbcTemplate().execute(SQL_DELETE_BY_LEVEL,
				new PreparedStatementCallback<Integer>() {
					@Override
					public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException,
							DataAccessException {
						ps.setString(1, level);
						return ps.executeUpdate();
					}
				});
	}

	/**
	 * 获取子类列表
	 * 
	 * @param parentId
	 * @return
	 */
	public List<Org> getChildren(Long parentId) {
		return queryForList(createCriteria(Restrictions.eq("parentId", parentId)).addOrder(
				Order.asc("orderNum")));
	}

	/**
	 * 判断子类是否叶子节点
	 * 
	 * @param parentId
	 * @return {id,is_leaf}
	 */
	public SqlRowSet queryChildrenIsLeaf(final Long parentId) {
		return getJdbcTemplate().queryForRowSet(SQL_LIST_CHILD_IS_LEAF, parentId);
	}
}
